package com.jpz.controller;


import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.mapper.EntityWrapper;
import com.baomidou.mybatisplus.mapper.Wrapper;
import com.baomidou.mybatisplus.plugins.Page;
import com.jpz.bean.Grade;
import com.jpz.bean.Student;
import com.jpz.service.GradeService;
import com.jpz.service.StudentService;
import com.jpz.utils.CreateFileUtils;
import com.jpz.utils.DateUtils;
import com.jpz.utils.ExcelUtils;
import com.jpz.utils.pageshow;

import java.io.File;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.List;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;

/**
 * <p>
 *  前端控制器
 * </p>
 *
 * @author jpz
 * @since 2018-11-08
 */
@Controller
@RequestMapping("/student")
public class StudentController {
	@Autowired
	StudentService stuservice;
	@Autowired
	GradeService gradeservice;
	@RequestMapping("/show")
	public String show(Model model,int pageindex,String name,Integer gid) {
		if("".equals(name)) {
			name=null;
		}
	
		Page<Student> page = new Page<>(pageindex, 4);
		Page<Student> selectPage=null;
		if(null!=name && gid==null) {
			selectPage = stuservice.selectPage(page, new EntityWrapper<Student>().like("name", name).eq("del", 0));
		}else if(gid!=null && name==null) {
			selectPage = stuservice.selectPage(page, new EntityWrapper<Student>().eq("gid", gid).eq("del", 0));
		}else if(null!=name && gid!=null) {
			selectPage = stuservice.selectPage(page, new EntityWrapper<Student>().like("name", name).eq("gid", gid).eq("del", 0));
		}else {
			selectPage = stuservice.selectPage(page,new EntityWrapper<Student>().eq("del", 0));
		}
		List<Student> list = selectPage.getRecords();
		for (Student stu : list) {
			Grade grade = gradeservice.selectById(stu.getGid());
			stu.setGrade(grade);
		}
		//获取所有班级在页面搜索
		List<Grade> gradelist = gradeservice.selectList(null);
		long total = selectPage.getTotal();
		int tol = new Long(total).intValue();
		Student student = new Student(name, gid);
		pageshow<Student> pageshow = new pageshow<>(list, tol, student, selectPage.getCurrent());
		model.addAttribute("pagebean", pageshow);
		model.addAttribute("gradelist", gradelist);
		return "/studentlist.jsp";
	}
	@RequestMapping("/update")
	public String update(int sid,Model model) {
		//获取学生对象用于回显
		Student student = stuservice.selectById(sid);
		//获取所有班级用于回显
		List<Grade> glist = gradeservice.selectList(null);
		//将数据存入其中用来回显
		model.addAttribute("student", student);
		model.addAttribute("glist", glist);
		return "/studentupdate.jsp";
	}
	@RequestMapping("/reallyup")
	public String reallyup(int sid,Model model,Student stu,String sex) {
		boolean updateById = stuservice.updateById(stu);
		return "redirect:/student/show?pageindex=1";
	}
	@RequestMapping("/xiangxi")
	public String xiangxi(int sid,Model model) {
		Student student = stuservice.selectById(sid);
		Grade grade = gradeservice.selectById(student.getGid());
		student.setGrade(grade);
		model.addAttribute("stu", student);
		return "/studentdetails.jsp";
	}
	@RequestMapping("/del")
	public String del(int sid,Model model) {
		Student student = stuservice.selectById(sid);
		student.setDel(1);
		boolean updateById = stuservice.updateById(student);
		return "redirect:/student/show?pageindex=1";
	}
	@RequestMapping("/add")
	public String add(Model model) {
		List<Grade> glist = gradeservice.selectList(null);
		model.addAttribute("glist", glist);
		return "/studentadd.jsp";
	}
	@RequestMapping("/radd")
	public String radd(Model model,Student student) {
		boolean insert = stuservice.insert(student);
		if(insert) {
			return "redirect:/student/show?pageindex=1";
		}else {
			return "/student/add";
		}
	}
	//导入的前奏
	@RequestMapping("/goimport")
	public String goimport(Model model) {
		List<Grade> list = gradeservice.selectList(null);
		model.addAttribute("glist", list);
		return "/studentimport.jsp";
	}
	@RequestMapping("/importStudent")
	public void importstu(Integer gid, @RequestParam("mFile") MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
		System.out.println("上传的班级是：" + gid);
		String excelPath = "";
		File createDir = CreateFileUtils.createDirExcel(request.getSession().getServletContext());
		if (!file.isEmpty()) {
			String filename = file.getOriginalFilename();
			String createName = CreateFileUtils.createName(filename);
			File f = new File(createDir, createName);
			excelPath = "excel/" + new SimpleDateFormat("yyyy-MM-dd").format(Calendar.getInstance().getTime()) + "/" + createName;
			file.transferTo(f);
		}

		String realPath = request.getSession().getServletContext().getRealPath(excelPath);// 获取服务器的路径
		System.out.println("文件上传成功:" + realPath);

		List<Student> students = ExcelUtils.readFromExcel(realPath); // 上传成功之后读取学生集合 将数据加入数据库
		for (Student s : students) {
			s.setGid(gid);
			s.setDel(0);// 未删除
			System.out.println(s);
		}
		// 学生集合新增到数据库 采用的是批量新增的方法

		boolean b = stuservice.insertBatch(students); // 批量新增
		if (b) {
			response.getWriter().write("<script>alert('导入成功');location.href='" + request.getContextPath() + "/student/show?pageindex=1'</script>");
		} else {
			response.getWriter().write("<script>alert('导入失败');location.href='" + request.getContextPath() + "/student/show?pageindex=1'</script>");
		}
	}
	@RequestMapping("/export")
	public String export(Student s,HttpServletResponse response,HttpServletRequest request) throws Exception {
		//新建一个条件构造器  判断搜索
		System.out.println("获取的学生对象"+s);
		EntityWrapper<Student> wrapper = new EntityWrapper<Student>();
		if(s!=null && s.getName()!=null) {
			wrapper.like("name", s.getName());
		}
		if(s!=null && s.getGid()!=null && s.getGid()!=-1) {
			wrapper.eq("gid", s.getGid());
		}
		wrapper.eq("del", 0);
		//前面是条件 后面是获取的数据
		List<Student> list = stuservice.selectList(wrapper);
		for (Student student : list) {
			System.out.println("查询的集合"+student);
		}
		// 生成Excel表格 作为流返回(下载)

				// 先创建工作簿对象
				HSSFWorkbook wb = new HSSFWorkbook();
				// 创建工作表对象并命名
				HSSFSheet sheet = wb.createSheet("学生信息表");
				
				int rowCount = 0; // 行数 默认第一行
				sheet.addMergedRegion( new CellRangeAddress(0, 0, 0, 10)  ); //合并单元格
				HSSFRow rowTitle = sheet.createRow(rowCount++);
				HSSFCell cellTile = rowTitle.createCell(0);
				
				HSSFCellStyle cellStyle = wb.createCellStyle(); 
				cellStyle.setFillForegroundColor((short) 13);// 设置背景色  
				cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);  
				cellStyle.setAlignment(HorizontalAlignment.CENTER); // 居中  
				cellTile.setCellStyle(cellStyle);
				
				cellTile.setCellValue("学生信息表");
				
				
				HSSFRow rowHead = sheet.createRow(rowCount++);  //标题行
				String[] titiles={"学号","姓名","性别","出生日期","身份证号","毕业学校","学历","邮箱","QQ","电话","入学日期"};
				for (int i = 0; i < titiles.length; i++) {
					rowHead.createCell(i).setCellValue(titiles[i]);
				}
				
				// 遍历集合对象创建行和单元格
				for (int i = 0; i < list.size(); i++) {
					//将数据填充到单元格中
					
					Student student = list.get(i);// 取出Student对象
					// 创建行
					HSSFRow row = sheet.createRow(rowCount++);
					// 开始创建单元格并赋值
					HSSFCell noCell = row.createCell(0); // 创建第一个单元格
					noCell.setCellValue(student.getNo());

					HSSFCell nameCell = row.createCell(1);
					nameCell.setCellValue(student.getName());

					HSSFCell sexCell = row.createCell(2);
					sexCell.setCellValue(student.getSex());

					HSSFCell birthday = row.createCell(3);
					birthday.setCellValue(student.getBirthday());

					HSSFCell cardnoCell = row.createCell(4);
					cardnoCell.setCellValue(student.getCardno());

					HSSFCell schoolCell = row.createCell(5);
					schoolCell.setCellValue(student.getSchool());

					HSSFCell educationCell = row.createCell(6);
					educationCell.setCellValue(student.getEducation());

					HSSFCell emailCell = row.createCell(7);
					emailCell.setCellValue(student.getEmail());

					HSSFCell qqCell = row.createCell(8);
					qqCell.setCellValue(student.getQq());

					HSSFCell phoneCell = row.createCell(9);
					phoneCell.setCellValue(student.getPhone());

					HSSFCell dateCell = row.createCell(10);
					dateCell.setCellValue(student.getCreatedate());
				}

				String fileName="学生信息表"+System.currentTimeMillis()+".xls";//文件名
			
				//生成Excel并提供下载
				String userAgent=request.getHeader("User-Agent");
				if(userAgent.contains("Safari")){
					response.addHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "UTF-8")) ;
					
				}else{
					response.addHeader("Content-Disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"ISO-8859-1")) ;
					
				}
				ServletOutputStream out = response.getOutputStream(); //输出流
				wb.write(out);
				out.close();//关闭流
				/*response.getWriter().write("<script>alert('导出成功！');location.href='" + request.getContextPath() + "/student/list/1'</script>");*/
				return "redirect:/student/shhow?pageindex=1";
	}
	

}

